package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import db.DbController;

public class DbUsers {

    public static boolean deleteUser(String username) {
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        boolean bOK = false;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_id from m_users where m_username=N'" + username + "'");
            rs = pstat.executeQuery();
            if (rs.next()) { // if user exists
                int userID = rs.getInt("m_id");
                rs.close();
                pstat.close();
                // delete from m_approvings table
                pstat = cnn.prepareStatement("delete from m_approvings where m_userid=" + userID);
                pstat.executeUpdate();
                pstat.close();
                // delete from m_managerindict table
                pstat = cnn.prepareStatement("delete from m_managerindict where m_userID=" + userID);
                pstat.executeUpdate();
                pstat.close();
                // delete from m_usrs table
                pstat = cnn.prepareStatement("delete from m_users where m_id=" + userID);
                pstat.executeUpdate();
                pstat.close();
                bOK = true;
            }
        } catch (Exception ex) {
            LOG.error("deleteUser", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return bOK;
    }

   
    ///////////////////////////////////
    public static ResultSet getRoleAndID(String username, String password) {
    	
    }
    
    public static ResultSet getID(String username) {
    	
    }
    
    public static int insert(String username, String password, int role) {
    	
    	Connection cnn = null;
        CallableStatement cstat = null;
        
        try {
            cnn = DbController.getConnection();
            cstat = cnn.prepareCall("{call m_users_insert(?,?,?)}");
            cstat.setString(1, username);
            cstat.setString(2, password);
            cstat.setInt(3, role);

            
            return cstat.executeUpdate();
        }
        
        catch (SQLException e) {
			// TODO: handle exception
        	return 0;
		}       
    }
    
    public static int updateDate(int userID) {
    	
    	Connection cnn = null;
        CallableStatement cstat = null;
        
        try {
            cnn = DbController.getConnection();
            cstat = cnn.prepareCall("{call update_users_dat(?)}");
            cstat.setInt(1, userID);

            
            return cstat.executeUpdate();
        }
        
        catch (SQLException e) {
			// TODO: handle exception
        	return 0;
		}       
    }    

    public static int updatePassword(String username, String password, String newpass) {
       	Connection cnn = null;
        CallableStatement cstat = null;
        
        try {
            cnn = DbController.getConnection();
            cstat = cnn.prepareCall("{call update_dictionaries_delcount(?, ?, ?)}");
            cstat.setString(1, username);
            cstat.setString(2, password);
            cstat.setString(3, newpass);
            
            return cstat.executeUpdate();
        }
        
        catch (SQLException e) {
			// TODO: handle exception
        	return 0;
		}       
    }
}
